import json

import xlwt

from quote_folder.Db_connect import DB_connect

develop_con = DB_connect().online_db()  # 开发数据库连接
bigdata_con = DB_connect().online_big_data()
cur1 = develop_con.cursor()
cur2 = bigdata_con.cursor()

info_list = []

def get_money_type(sqh, item):
    sql = f"SELECT * FROM `big_data`.`annual_fee_information_0321` WHERE `application_number` = '{sqh}'"
    cur2.execute(sql)
    data = cur2.fetchall()
    try:
        strr = data[0][3]+"+"+data[1][3]
        money = int(float(data[0][4]) + float(data[1][4]))
        close_day = data[0][5]
        item['money_type'] = strr
        item['money'] = money
        item['close_date'] = close_day
    except:
        item['money_type'] = ""
        item['money'] = ""
        item['close_date'] = ""
        # print(item, data)
    return item


def get_data():
    sql = """SELECT * FROM `big_data`.`annual_fee_information_0321` WHERE `money_type` = '恢复权利请求费'"""
    cur2.execute(sql)
    data = cur2.fetchall()
    for i in range(len(data)):
        item = {'name': ""}
        sqh = data[i][1]
        item['sqh'] = sqh
        item['close_date'] = data[i][5]
        item = get_money_type(sqh, item)    # 获取费用信息
        item = get_is_stop(sqh, item)       # 获取是否停止监管
        if item['name'] and item['name'] != '不在系统监管专利内':
            sqh = item['sqh']
            item = get_marks(sqh, item)     # 确定是否放弃
            item = get_lianxr(item)         # 获取联系方式
            item['state'] = ''
            print(item)
            info_list.append(item)
    save_json_dict(info_list)


def get_is_stop(sqh, item):
    sql = f"SELECT name, is_stop, unique_number FROM `mykhadmin`.`finals` WHERE `application_number` = 'ZL{sqh}'"
    cur1.execute(sql)
    data = cur1.fetchall()
    try:
        if data[0][1] == 0:
            item['name'] = data[0][0]
            item['unique_number'] = data[0][2]
        else:
            pass
    except:
        item['name'] = "不在系统监管专利内"
    return item


def get_marks(sqh, item):
    sql = f"SELECT marks FROM `big_data`.`MoneyWarning` WHERE `sqh` = '{sqh}'"
    cur2.execute(sql)
    data = cur2.fetchall()
    marks = data[0][0]
    if marks:
        aa = json.loads(marks)
        if "放弃" in marks:
            item['marks'] = '放弃'
            item['time'] = ""
        else:
            item['marks'] = aa[0]['marks']
            item['time'] = aa[0]['insert_time']
    else:
        item['marks'] = ""
    return item


def get_lianxr(item):
    order_num = item['unique_number']
    sql = f"""SELECT patent_user, liaisons, liaisons_tel FROM `mykhadmin`.`orders` WHERE `order_number` = '{order_num}'"""
    cur1.execute(sql)
    data = cur1.fetchall()
    item['patent_user'] = data[0][0]
    item['lianxr'] = data[0][1]
    item['tel'] = data[0][2]
    return item


def save_json_dict(info_list):
    workbook = xlwt.Workbook()
    sheet = workbook.add_sheet("sheet1")
    sheet.write(0, 0, '序号')
    sheet.write(0, 1, '申请号')
    sheet.write(0, 2, '专利名字')
    sheet.write(0, 3, '专利权人')
    sheet.write(0, 4, '费用类型')
    sheet.write(0, 5, '金额')
    sheet.write(0, 6, '截止日')
    sheet.write(0, 7, '状态')
    sheet.write(0, 8, '联系人')
    sheet.write(0, 9, '电话')
    sheet.write(0, 10, "备注")
    sheet.write(0, 11, '备注时间')
    if len(info_list) > 0:
        for i in range(len(info_list)):
            sheet.write(i + 1, 0, i + 1)
            sheet.write(i + 1, 1, info_list[i]['sqh'])
            sheet.write(i + 1, 2, info_list[i]['name'])
            sheet.write(i + 1, 3, info_list[i]['patent_user'])
            sheet.write(i + 1, 4, info_list[i]['money_type'])
            sheet.write(i + 1, 5, info_list[i]['money'])
            sheet.write(i + 1, 6, info_list[i]['close_date'])
            sheet.write(i + 1, 7, info_list[i]['state'])
            sheet.write(i + 1, 8, info_list[i]['lianxr'])
            sheet.write(i + 1, 9, info_list[i]['tel'])
            sheet.write(i + 1, 10, info_list[i]['marks'])
            sheet.write(i + 1, 11, info_list[i]['time'])
        workbook.save('./恢复费案子.xls')


if __name__ == '__main__':
    get_data()

    # get_marks("202020353203X", {})
